One of the reasons behind the unavailability of anti-impotent was that people in that age was viagra sildenafil buy too shy to discuss or open the problem in front of anyone. I must say that this prices for cialis medicine kamagra jelly online. Due to these properties it alleviates sildenafil prescription pitta and vata . Include these foods to the regular diet to overcome the symptoms of this sexual disorder. online viagra
How to Use a Select Query with Different Type of Row Total
A Select query is created from a simple query by adding a Row Total in the query design. There are many types of Row Total that can be selected from the list such as Group By, Sum, Average, Min, Max, etc. By using this query, we can find the sum (total), average, min, or max on that field from a group of data.
In this How To, I will show how to find the Sum (total), Average, or Max of each product, each customer, or each customer type.
Step by Step of Creating a Select Query
Step #1 Click Query Design under the Create Menu, then add Products and Orders table.
Step #2 Select ProductName and Description field from Products table and Amount field from Orders table as shown in the picture below.
Step #3 Click Crosstab icon of query type on the Create menu bar. Now you will see a Row Total and Row Crosstab. There are no Group By no the Row Total before we click on Crosstab query type as shown on the picture below.
Step #4 Click Select Qeury Type icon next to Make Table query type icon on the left side of Crosstab query icon. The Row Total still shows on the query design view, but not the Row Crosstab. We don’t need the Row Crosstab for this How To anyway.
After we click on view this query, there are 8 orders with the order amount for each product name. Per list below, you will see 3 products are duplicated because of using Group By on each field above.
Find Total (Sum) Order Amount for each Product
Change Group By to “Sum” on the Row Total of the Amount field under the query design. Leave Group By on the Row Total of the ProductName and Description column.
After clicking on query view, now there will be 5 rows with the total order amount for each product name with no duplicate product name. You will see the order amount of duplicate product name is added together. For example, 2 orders for Northwind Traders Beers (18 + 28) are 46.
Find Average Order Amount for each Product
Select “Avg” on the Row Total of the Amount field under the query design view. Leave Group By on the Row Total of the ProductName and Description column.
On query view, there are 5 rows with the average order amount for each product. For example, there are 2 orders for Northwind Traders Beers (18 + 28) and the average is $23.00 (18+28= 46/2 = 23). If there is one order for a product like Northwind Traders Corn the average of one order will be $30.00 (30/1 = 30).
Find Count (How many number of Order) for each Product
- Change a field name from Amount to “OrderNo”.
- Select “Count” on the Row Total of the Amount field under the query design view. Leave Group By on the Row Total of the ProductName and Description column.
Result on query view, there are 5 rows with the number of orders for each product. For example, there are 2 orders for Northwind Traders Beers then the CountOfOrderNo column will be 2 as shown in the picture.
Find Total for Each Category with Product
- Delete Description column
- Insert a “Category” field in front of ProductName column
- Change OrderNo to “Amount” on the Orders table column
- Select “Sum” on the Row Total of the Amount field under the query design view. Leave Group By on the Row Total of the Category and ProductName column.
After clicking on Query View, there will be 5 rows with the total order amount for each product with no duplicate product. You will see a duplicate category of Drinks with two different products because we leave the ProductName field with a Row Total of Group By. If you just want to get the total order amount for category then you need to delete the ProductName column and leave only Category column.
Find Total for Each Category only
Delete ProductName column and leave only the Category column as shown below.
After clicking on Query View, there will be 4 rows with the total order amount for each category of product. Now there is no duplicate category for Drinks. However, the total of order for Drinks category is $79.00 (14 + 65).
Find Total Order Amount for Each Customer
- Delete Category column
- Insert a “CustomerID” field from Orders table in front of Amount column
- Leave the Amount field with the Sum in the Row Total line
Figure 1 (Orders table)
Per Figure 1 above, there are 8 orders and John Smith has 3 orders. On Query View of Find Total Order Amount for each customer, there are only 4 records shown on the list grouped by customer. John Smith has total order in the amount of $68.00 (30+20+18).
Find the Max Order Amount for Each Customer
- Change Sum to “Max” on the Row Total of Amount column
- Select “Descending” on the Row Sort of Amount column
On Query view, this query will display only the Max order amount for each customer. Per Figure 1, John Smith has 3 orders: 30, 20, and 18. This query will display $30.00 for John Smith. The amount of $30.00 is the max amount from his 3 orders.
Find the Total Order Amount for Each Customer Type
- Delete Products table
- Insert tbl_Customer table and tbl_Customer_Type table
- Insert Customer_type field from tbl_Customer_Type table in front of Amount column
- Select “Sum” on the Row Total
- Select “Descending” on the Row Sort to list the bigger order amount first
Per Figure 1 above, there are 8 orders from all customers. On Query View, there are only 3 customer types for those customers who have orders. The total of each customer type is displayed from the greatest to the smallest amount as specified with Descending on Row Sort. Per Figure 1, John Smith and Paul Smith have 5 orders in total of $146.00 (30+20+18+50+28).
